-- @Create_time :2023-08-28 10:00
-- @Author      :kumiler
-- @emial       :lukunming@jtexpress.com
-- @File        :ddl.hql
-- @Desc        :中心应付派费表

-- 中心应付派费明细
create external table spmi_dm.dm_spmi_center_piece_fee_report_detail_dt
(
    waybill_no                  string comment '运单号',
    waybill_time                string comment '运单产生时间',
    sign_time                   string comment '签收时间',
    piece_network_code          string comment '派件网点编码',
    piece_network_name          string comment '派件晚点名称',
    piece_franchisee_code       string COMMENT '派件加盟商编码',
    piece_franchisee_name       string COMMENT '派件加盟商名称',
    piece_financial_center_code string COMMENT '派件财务中心编码',
    piece_financial_center_name string COMMENT '派件财务中心名称',
    sender_province_id          bigint COMMENT '寄件省份ID',
    sender_province_name        string COMMENT '寄件省份名称',
    BF006                       decimal(10, 3) comment '中心应付派费',
    BF012                       decimal(10, 3) comment '中心应付派费',
    waybill_num                 bigint comment '票数'
) comment '中心应付派费报表明细'
    partitioned by (dt string comment '时间分区-签收时间yyyy-MM-dd')
    ROW FORMAT SERDE 'org.apache.hadoop.hive.ql.io.parquet.serde.ParquetHiveSerDe'
        WITH SERDEPROPERTIES (
        'serialization.format' = '1'
        )
    STORED AS
        INPUTFORMAT 'org.apache.hadoop.hive.ql.io.parquet.MapredParquetInputFormat'
        OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.parquet.MapredParquetOutputFormat'
    LOCATION '/dw/hive/spmi_dm.db/external/dm_spmi_center_piece_fee_report_detail_dt'
;

-- 中心应付派费汇总
CREATE EXTERNAL TABLE spmi_dm.dm_spmi_center_piece_fee_report_aggr_dt
(
    id                          string comment 'tidb自增字段',
    piece_network_code          string COMMENT '派件网点编码',
    piece_network_name          string COMMENT '派件晚点名称',
    piece_franchisee_code       string COMMENT '派件加盟商编码',
    piece_franchisee_name       string COMMENT '派件加盟商名称',
    piece_financial_center_code string COMMENT '派件财务中心编码',
    piece_financial_center_name string COMMENT '派件财务中心名称',
    sender_province_id          bigint COMMENT '寄件省份ID',
    sender_province_name        string COMMENT '寄件省份名称',
    bf006                       decimal(10, 3) COMMENT '中心应付派费',
    bf012                       decimal(10, 3) COMMENT '中心应付派费',
    waybill_num                 bigint COMMENT '票数'
)
    COMMENT '中心应付派费报表-网点聚合'
    PARTITIONED BY (dt string COMMENT '时间分区-签收时间yyyy-MM-dd')
    ROW FORMAT SERDE 'org.apache.hadoop.hive.ql.io.parquet.serde.ParquetHiveSerDe'
        WITH SERDEPROPERTIES (
        'serialization.format' = '1'
        )
    STORED AS
        INPUTFORMAT 'org.apache.hadoop.hive.ql.io.parquet.MapredParquetInputFormat'
        OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.parquet.MapredParquetOutputFormat'
    LOCATION '/dw/hive/spmi_dm.db/external/dm_spmi_center_piece_fee_report_aggr_dt'
;

-- 中心应付网点聚合临时表
create external table spmi_tmp.dm_spmi_center_piece_fee_report_aggr_dt (
    id string comment '',
    piece_network_code          string comment '派件网点编码',
    piece_network_name          string comment '派件晚点名称',
    piece_franchisee_code       string COMMENT '派件加盟商编码',
    piece_franchisee_name       string COMMENT '派件加盟商名称',
    piece_financial_center_code string COMMENT '派件财务中心编码',
    piece_financial_center_name string COMMENT '派件财务中心名称',
    sender_province_id          bigint COMMENT '寄件省份ID',
    sender_province_name        string COMMENT '寄件省份名称',
    BF009                       decimal(10, 3) comment '中心应付派费',
    BF012                       decimal(10, 3) comment '中心应付派费',
    waybill_num                 bigint comment '票数',
    dt string comment '数据签收日期',
    waybill_time string comment '运单日期',

) comment '中心应付网点聚合临时表'
ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.avro.AvroSerDe'
WITH SERDEPROPERTIES (
  'serialization.format' = '1'
)
STORED AS
  INPUTFORMAT 'org.apache.hadoop.hive.ql.io.avro.AvroContainerInputFormat'
  OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.avro.AvroContainerOutputFormat'
LOCATION '/dw/hive/spmi_tmp.db/external/dm_spmi_center_piece_fee_report_aggr_dt'
;
